package com.baturu.vin.benz.cleanData.dao;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 处理奔驰车型DAO
 * @Author: jiangxinlei
 * @Time: 2018-05-17 9:41
 **/
public interface BenzCarTypeDAO {

    @Update("DROP TABLE IF EXISTS tmp_benz_cartype_all")
    int dropTmpBenzCarTypeAll();

    @Update({"CREATE TABLE `tmp_benz_cartype_all` (",
            "`id` int(11) NOT NULL AUTO_INCREMENT,",
            "`whc` char(3) DEFAULT NULL,",
            "`chassis` char(6) DEFAULT NULL,",
            "`engine` char(6) DEFAULT NULL,",
            "`transmission` char(6) DEFAULT NULL,",
            "`release_date` varchar(50) DEFAULT NULL,",
            "`delivery_date` varchar(50) DEFAULT NULL,",
            "`location` varchar(50) DEFAULT NULL,",
            "`SACODE_DATA` varchar(1440) DEFAULT NULL,",
            "`displacement` varchar(255) DEFAULT NULL,",
            "`transmissionType` varchar(255) DEFAULT NULL,",
            "`transmissionDesc` varchar(255) DEFAULT NULL,",
            "`gearsNum` varchar(255) DEFAULT NULL,",
            "PRIMARY KEY (`id`),",
            "UNIQUE KEY `unique_key` (`whc`,`chassis`,`engine`,`transmission`,`release_date`,`delivery_date`,`location`)",
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8"})
    int createTmpBenzCarTypeAll();

    @Insert({"insert IGNORE into tmp_benz_cartype_all(`whc`,`chassis`,`engine`,`transmission`,`release_date`,`delivery_date`,",
            "`location`,`SACODE_DATA`)",
            "select distinct a.whc, a.chassbm as chassis, SUBSTR(a.`engine`,1,6) as `engine`, ",
            "substr(a.transmission,1,6) as TRANSMISSION, a.RELEASE_DATE, a.DELIVERY_DATE,b.LOCATION,",
            "c.SACODE_DATA from ${tableName} as a",
            "JOIN dc_map_v_newdata as b on a.WHC=b.WHC and a.CHASSBM=b.CHASSBM and a.CHASS_IDENT= b.CHASS_IDENT",
            "join ${type3Table} as c on a.WHC=c.WHC and a.CHASSBM = c.CHASSBM and a.CHASS_IDENT =c.CHASS_IDENT"})
    int insertTmpBenzCarTypeAll(@Param("tableName") String tableName, @Param("type3Table") String type3Table);

    @Select("select max(id) from tmp_benz_cartype_all")
    int maxId();

    @Select({"select `id`,`chassis`,`engine`,`transmission`,`release_date`,`delivery_date`,`SACODE_DATA` as saCode from tmp_benz_cartype_all where id>#{fid} and id<=#{lid}"})
    List<Map<String,String>> queryTmpBenzCarTypeAllById(@Param("fid") Integer fid, @Param("lid") Integer lid);

    @Insert({"<script>insert into tmp_benz_cartype_all(id, displacement, transmissionType,transmissionDesc, gearsNum) values",
            "<foreach item='item' index='index' collection='disTrans' open='' separator=',' close=''>",
            "(#{item.id},#{item.displacement},#{item.transmissionType},#{item.transmissionDesc},#{item.gearsNum})",
            "</foreach>",
            "on duplicate key update",
            "displacement=values(displacement),",
            "transmissionType=values(transmissionType),",
            "transmissionDesc=values(transmissionDesc),",
            "gearsNum=values(gearsNum)",
            "</script>"})
    int dealDisAndTrans(@Param("disTrans") List<Map<String, String>> disTrans);


    @Update("DROP TABLE IF EXISTS tmp_benz_cartype_")
    int dropTmpBenzCarType();

    @Update("CREATE TABLE `tmp_benz_cartype_` (\n" +
            "  `whc` char(3) DEFAULT NULL,\n" +
            "  `chassis` char(6) DEFAULT NULL,\n" +
            "  `engine` char(6) DEFAULT NULL,\n" +
            "  `transmission` char(6) DEFAULT NULL,\n" +
            "  `release_date` varchar(50) DEFAULT NULL,\n" +
            "  `delivery_date` varchar(50) DEFAULT NULL,\n" +
            "  `location` varchar(50) DEFAULT NULL,\n" +
            "  `displacement` varchar(255) DEFAULT NULL,\n"+
            "`transmissionType` varchar(255) DEFAULT NULL,"+
            "`transmissionDesc` varchar(255) DEFAULT NULL,"+
            "`gearsNum` varchar(255) DEFAULT NULL,"+
            "    UNIQUE KEY `unique_key` (`whc`,`chassis`,`engine`,`transmission`,`release_date`,`delivery_date`,`location`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8")
    int createTmpBenzCarType();

    @Insert({"insert IGNORE into tmp_benz_cartype_",
            "select distinct `whc`,`chassis`,`engine`,`transmission`,`release_date`,`delivery_date`,`location`,",
            "`displacement`,`transmissionType`,`transmissionDesc`,`gearsNum` from tmp_benz_cartype_all"})
    int insertTmpBenzCarType();

    @Update("update ignore tmp_benz_cartype_\n" +
            "    set RELEASE_DATE = DELIVERY_DATE\n" +
            "    where RELEASE_DATE = ''\n" +
            "    or RELEASE_DATE is null")
    int updateReleaseDate();

    @Delete("DELETE from tmp_benz_cartype_\n" +
            "    where release_date is null or release_date = ''")
    int deleteNoReleaseDate();

    @Update("update ignore tmp_benz_cartype_\n" +
            "    set RELEASE_DATE = DELIVERY_DATE\n" +
            "    where LENGTH(RELEASE_DATE)<6\n" +
            "    and LENGTH(DELIVERY_DATE)>=6")
    int dealReleaseDate1();

    @Update("update tmp_benz_cartype_\n" +
            "    set RELEASE_DATE = SUBSTR(RELEASE_DATE,3,6)\n" +
            "    where LENGTH(RELEASE_DATE)>=8")
    int dealReleaseDate2();

    @Delete("DELETE from tmp_benz_cartype_\n" +
            "    where LENGTH(RELEASE_DATE)<6")
    int deleteReleaseDate();

    @Update("drop table if EXISTS tmp_benz_cartype_ex")
    int dropCarTypeEx();

    @Update("CREATE TABLE `tmp_benz_cartype_ex` (\n" +
            "  `WHC` char(3) DEFAULT NULL,\n" +
            "  `CHASSIS` char(6) DEFAULT NULL,\n" +
            "  `engine` char(6) DEFAULT NULL,\n" +
            "  `TRANSMISSION` char(6) DEFAULT NULL,\n" +
            "  `YEAR` int(11) DEFAULT '0',\n" +
            "  `LOCATION` varchar(50) DEFAULT NULL,\n" +
            "  `country` varchar(10) DEFAULT '',\n" +
            "  `appinf` char(1) DEFAULT '1',\n" +
            "  `carType` varchar(100) DEFAULT '',\n" +
            "  `displacement` varchar(255) DEFAULT NULL,\n"+
            "`transmissionType` varchar(255) DEFAULT NULL,"+
            "`transmissionDesc` varchar(255) DEFAULT NULL,"+
            "`gearsNum` varchar(255) DEFAULT NULL"+
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8")
    int createCarTypeEx();

    @Insert("insert into tmp_benz_cartype_ex(WHC,CHASSIS,`engine`,TRANSMISSION,`YEAR`,LOCATION,`displacement`,`transmissionType`,`transmissionDesc`,`gearsNum`)"+
        " SELECT DISTINCT WHC,CHASSIS,`engine`,TRANSMISSION,SUBSTR(RELEASE_DATE,1,2) AS `YEAR`,LOCATION,`displacement`,`transmissionType`,`transmissionDesc`,`gearsNum` FROM tmp_benz_cartype_")
    int insertCarTypeEx();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set `year` = 1900+`year`\n" +
            "    where `year`>59")
    int updateYear1();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set `year` = 2000+`year`\n" +
            "    where `year`<=59")
    int updateYear2();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set country = '欧洲'\n" +
            "    where SUBSTR(whc,1,1) ='W'")
    int updateCountry1();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set country = '中国'\n" +
            "    where SUBSTR(whc,1,1) ='L'")
    int updateCountry2();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set country = '巴西'\n" +
            "    where whc ='9BM'")
    int updateCountry3();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set country = '美国'\n" +
            "    where WHC='4JG'")
    int updateCountry4();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set country = '墨西哥'\n" +
            "    where whc='3MB'")
    int updateCountry5();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set appinf = 'F'\n" +
            "    where LOCATION IN ('PNA','CNA','NA')")
    int updateAppInf1();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set appinf = 'S'\n" +
            "    where LOCATION IN ('PJA','CJA','JA')")
    int updateAppInf2();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set appinf = 'W'\n" +
            "    where LOCATION IN ('PLA','CLA','LA')")
    int updateAppInf3();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set appinf = 'K'\n" +
            "    where LOCATION IN ('PSA','CSA','SA')")
    int updateAppInf4();

    @Update("UPDATE tmp_benz_cartype_ex\n" +
            "    set appinf = 'M'\n" +
            "    where LOCATION IN ('PSM','CSM','SM')")
    int updateAppInf5();

    @Update("update tmp_benz_cartype_ex as a, models_v_newdata as b\n" +
            " set a.carType = b.SALESDES\n" +
            " where b.CLASS in ('1','2','3','F')\n" +
            " and b.TYPE = substr(a.chassis,1,3)\n" +
            " and b.SUBBM1 = SUBSTR(a.chassis,4,3)\n" +
            " and b.APPINF like concat('%',a.appinf,'%')\n" +
            " and EXISTS (select 1 from appl_cat_base_v_newdata as c where c.catnum = b.CATNUM and c.markets = b.APPINF)")
    int updateCarType();

    @Update("update tmp_benz_cartype_ex\n" +
            "    set `engine` = ''\n" +
            "    where LENGTH(`engine`) <6 or `engine` is NULL")
    int updateEngine();

    @Update("update tmp_benz_cartype_ex\n" +
            "    set `transmission` = ''\n" +
            "    where LENGTH(`transmission`) <6 or TRANSMISSION is NULL")
    int updateTrans();

    @Update("drop table if EXISTS tmp_benz_vininfo_btr")
    int dropTmpBenzVinInfoBtr();

    @Update("create table tmp_benz_vininfo_btr like benz_vininfo_btr")
    int createVinBtr();

    @Insert("insert into  tmp_benz_vininfo_btr (carType,productTime,country,`engine`,transmission,chassis,displacement,transmissionType,gearsNum)\n" +
            "    select DISTINCT carType,`YEAR` as productTime, country, `engine`, transmission, chassis,displacement,transmissionType,gearsNum from tmp_benz_cartype_ex\n" +
            "    where carType <>'' and `YEAR`>=1990 and country <>''")
    int insertVinBtr();

    @Update("update tmp_benz_vininfo_btr set displacement='' where displacement is null ")
    int updateNullToEmpty1();
    @Update("update tmp_benz_vininfo_btr set transmissionType='' where transmissionType is null ")
    int updateNullToEmpty2();
    @Update("update tmp_benz_vininfo_btr set gearsNum='' where gearsNum is null ")
    int updateNullToEmpty3();

    @Update({"update tmp_benz_vininfo_btr as a ,benz_vininfo_btr as b",
            "set a.btrid = b.btrId",
            "where a.cartype = b.carType",
            "and a.producttime = b.productTime",
            "and a.country = b.country",
            "and a.`engine` = b.`engine`",
            "and a.transmission = b.transmission",
            "and a.chassis= b.chassis",
            "and a.displacement=b.displacement",
            "and a.transmissionType=b.transmissionType",
            "and a.gearsNum=b.gearsNum"})
    int updateExistsBtr();

    @Delete("delete from tmp_benz_vininfo_btr where btrId != '' or btrId is not null")
    int deleteExistsBtr();

    @Select("select id from tmp_benz_vininfo_btr where btrId='' or btrId is null")
    List<String> needDealBtr();

    @Insert({"<script>insert into tmp_benz_vininfo_btr(id, btrId)",
            "values",
            "<foreach item='item' index='index' collection='btrMap' open='' separator=',' close=''>",
            "(#{item.id}, #{item.btrId})",
            "</foreach>",
            "on duplicate key update",
            "btrId=values(btrId)",
            "</script>"})
    int updateBtr(@Param("btrMap") List<Map<String,String>> btrMap);

    @Update("drop table if EXISTS tmp_qp_cartype_benz")
    int dropQpCarType();

    @Update("create table tmp_qp_cartype_benz like qp_cartype_benz")
    int createQpCarType();

    @Insert({"insert into tmp_qp_cartype_benz(btrId,btrcountry,cartype,mj,producttime,enginetype,chassis,displacement,transmissionType,gearsNum)",
            "select distinct btrId,country,carType,productTime,productTime,`engine`,chassis,displacement,transmissionType,gearsNum from tmp_benz_vininfo_btr",
            "where btrId like CONCAT(#{btrPre},'%')"})
    int insertQpCarType(@Param("btrPre") String btrPre);

    @Delete("DELETE from tmp_qp_cartype_benz\n" +
            "    where btrcountry = ''")
    int deleteNoCountry();

    @Select("SELECT DISTINCT btrSubBrand, btrBrand, carSystem,carSystemId,"+
            "IF(LOCATE(\"AMG\",CarType), \"AMG\",\"\") as carType,"+
            " btrCountry,substr(Chassis, 1, 3) as chassis FROM `qp_cartype_benz`")
    List<Map<String, String>> queryTmpCarSystem();

    @Select({"select distinct btrId,btrCountry,carType,chassis from tmp_qp_cartype_benz"})
    List<Map<String, String>> queryTmpCarType();

    @Update({"<script>insert into tmp_qp_cartype_benz(btrId, carSystem, btrBrand, btrSubBrand, carSystemId)",
            "values",
            "<foreach item='item' index='index' collection='btrMap' open='' separator=',' close=''>",
            "(#{item.btrId},#{item.carSystem},#{item.btrBrand},#{item.btrSubBrand},#{item.carSystemId})",
            "</foreach>",
            "on duplicate key update",
            " carSystem=values(carSystem), btrBrand=values(btrBrand),btrSubBrand=values(btrSubBrand),",
            "carSystemId=values(carSystemId)",
            "</script>"})
    int updateCarSystem(@Param("btrMap") List<Map<String, String>> btrMap);

    @Update("update tmp_qp_cartype_benz as a ,qp_cartype_benz as b\n" +
            "    set a.BTRBrand = b.BTRBrand,a.BTRSubBrand=b.BTRSubBrand\n" +
            "    where a.BTRCountry = b.BTRCountry\n" +
            "    and a.CarSystem = b.CarSystem")
    int updateBrand();

    @Update("update tmp_qp_cartype_benz as a ,qp_cartype_benz as b\n" +
            "    set a.CarSystemId = b.CarSystemId\n" +
            "    where a.BTRBrand = b.BTRBrand and a.BTRSubBrand=b.BTRSubBrand and a.CarSystem = b.CarSystem")
    int updateCarSystemId();

    @Update("update tmp_qp_cartype_benz\n" +
            " set DriveType = if(locate('4MATIC',CarType),'四驱','两驱')")
    int updateDriveType();

    @Update("update tmp_qp_cartype_benz\n" +
            "    set brand = BTRSubBrand")
    int updatexxx();

    @Update("update tmp_qp_cartype_benz\n" +
            "    set Country = BTRCountry")
    int updateCountry();


    @Update("UPDATE tmp_qp_cartype_benz\n" +
            "SET TransmissionDesc = '自动变速器(AT)'"+
            " where transmissionType='自动'")
    int updateBTRTrans();

    @Update("UPDATE tmp_qp_cartype_benz\n" +
            "SET TransmissionDesc = '手动变速器(MT)'"+
            " where transmissionType='手动'")
    int updateBTRTrans2();

    @Update("UPDATE tmp_qp_cartype_benz\n" +
            "SET TransmissionDesc = ''"+
            " where transmissionType is null")
    int updateBTRTrans3();

    @Update("UPDATE tmp_qp_cartype_benz "+
            "set salesName = trim(concat(ifnull(displacement,''),' ',ifnull(transmissionDesc,''), ' ',carType)),"+
            "showCarType = trim(concat(ifnull(displacement,''),' ',ifnull(transmissionDesc,''), ' ',carType))")
    int updateSalesName();


    @Select({"SELECT DISTINCT a.btrId,b.displacement, b.transmissionType,b.transmissionDesc,b.gearsNum",
            " from tmp_benz_vininfo_btr as a ",
            "JOIN `tmp_benz_cartype_ex` as b",
            "on a.carType =b.carType and a.productTime=b.`YEAR`",
            " and a.country =b.country and a.`engine`=b.`engine`",
            " and a.transmission=b.TRANSMISSION and a.chassis=b.CHASSIS"})
    List<Map<String, String>> queryBtrCarType();

}
